package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Cart;
import org.csu.mypetstore.domain.CartItem;
import org.csu.mypetstore.domain.Item;
import org.csu.mypetstore.persistence.CartDAO;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.persistence.ItemDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Iterator;

public class CartDAOImpl implements CartDAO {
    private static final String GET_CART_BY_USER_ID =
            "SELECT * FROM cart WHERE userid = ?";

    private static final String REMOVE_CART_BY_USERID =
            "DELETE FROM cart WHERE userid = ?";

    private static final String REMOVE_ITEM_BY_UID_AND_IID=
            "DELETE FROM cart WHERE userid = ? AND itemid = ?";

    private static final String ADD_ITEM =
            "INSERT INTO cart (userid, itemid, quantity) VALUES (?,?,?)";

    private static final String INCREMENT =
            "UPDATE cart SET quantity = quantity + 1 WHERE userid = ? AND itemid = ?";

    private static final String DECREMENT =
            "UPDATE cart SET quantity = quantity - 1 WHERE userid = ? AND itemid = ?";

    private static final String GET_ITEM =
            "SELECT quantity FROM cart WHERE userid = ? AND itemid = ?";

    private static final String CONTAINS =
            "SELECT userid,quantity FROM cart WHERE userid = ? AND itemid = ?";

    public Cart getCartByUserId(String userId) {
        Cart cart = new Cart();
        try{
            ItemDAO itemDAO = new ItemDAOImpl();
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_CART_BY_USER_ID);
            preparedStatement.setString(1,userId);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                Item item = itemDAO.getItem(resultSet.getString("itemid"));
                int quantity = resultSet.getInt("quantity");
                for(int i = 0;i<quantity;i++) {
                    cart.addItem(item, true);
                }
            }

            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return cart;
    }

    public void removeCartByUserId(String userId) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(REMOVE_CART_BY_USERID);
            preparedStatement.setString(1,userId);
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }


    public void removeItemByUserIdAndItemId(String userId, String itemid) {
        try
        {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(REMOVE_ITEM_BY_UID_AND_IID);
            preparedStatement.setString(1,userId);
            preparedStatement.setString(2,itemid);
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }


    public void addItem(String userId, Item item){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(ADD_ITEM);
            preparedStatement.setString(1,userId);
            preparedStatement.setString(2,item.getItemId());
            preparedStatement.setInt(3,1);
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }

    }


    public void incrementQuantityByItemId(String userId, Item item) {
        try
        {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(INCREMENT);
            preparedStatement.setString(1, userId);
            preparedStatement.setString(2, item.getItemId());
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }


    public void decrementQuantityByItemId(String userId, Item item){
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement prepareStatement = connection.prepareStatement(GET_ITEM);
            prepareStatement.setString(1, userId);
            prepareStatement.setString(2, item.getItemId());
            ResultSet resultSet = prepareStatement.executeQuery();
            resultSet.next();
            int quantity = resultSet.getInt("quantity");
            if (quantity <= 1) {
                return;
            }

            prepareStatement = connection.prepareStatement(DECREMENT);
            prepareStatement.setString(1, userId);
            prepareStatement.setString(2, item.getItemId());
            prepareStatement.executeUpdate();

            DBUtil.closePreparedStatement(prepareStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }


    public void updateCartByUserId(String userId, Cart cart){
        try
        {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(REMOVE_CART_BY_USERID);
            preparedStatement.setString(1, userId);
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);

            Iterator<CartItem> cartItemIterator = cart.getAllCartItems();

            while (cartItemIterator.hasNext()) {
                CartItem cartItem = cartItemIterator.next();
                Item item = cartItem.getItem();
                int quantity = cartItem.getQuantity();
                addItem(userId, item);
                for (int i = 1; i < quantity; i++) {
                    incrementQuantityByItemId(userId, item);
                }
            }
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }


    public boolean containsItem(String userId, Item item) {
        try
        {
            Connection connection = DBUtil.getConnection();
            PreparedStatement prepareStatement = connection.prepareStatement(CONTAINS);
            prepareStatement.setString(1, userId);
            prepareStatement.setString(2, item.getItemId());
            ResultSet resultSet = prepareStatement.executeQuery();
            if (resultSet.next()) {
                DBUtil.closePreparedStatement(prepareStatement);
                DBUtil.closeConnection(connection);
                return true;
            } else {
                DBUtil.closePreparedStatement(prepareStatement);
                DBUtil.closeConnection(connection);
                return false;
            }
        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return false;
    }


    public int getQuantityById(String Pid,String Uid) {
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement prepareStatement = connection.prepareStatement(GET_ITEM);

            prepareStatement.setString(1, Uid);
            prepareStatement.setString(2, Pid);

            ResultSet resultSet = prepareStatement.executeQuery();
            resultSet.next();
            return resultSet.getInt("quantity");

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return 1;
    }
}
